package ru.fadeev.crammer.data;

import java.io.File;
import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.Statement;
import java.util.Date;
import java.util.TimeZone;
import java.util.Vector;

import jxl.BooleanCell;
import jxl.Cell;
import jxl.CellType;
import jxl.DateCell;
import jxl.Workbook;
import jxl.write.DateFormat; 
import jxl.write.DateTime; 
import jxl.write.Label; 
import jxl.write.WritableCellFormat; 
import jxl.write.WritableFont; 
import jxl.write.WritableSheet; 
import jxl.write.WritableWorkbook; 

import ru.fadeev.crammer.DatabaseManager;
import ru.fadeev.crammer.GFIndicesStorage;
import ru.fadeev.crammer.Settings;
import ru.fadeev.crammer.TestManager;
import ru.fadeev.crammer.Utils;


/**
 * DictionariesManager class represents one of the data entities of Crammer 
 * program. DictionariesManager provides functionality for managing dictionaries.
 * DictionariesManager is placed on top
 * of Crammer data structures pyramid: dictionaries mananger consists of 
 * dictionaries, dictionaries consist of sheets, sheets consist of word records. 
 * word record, besides translation information, contains word test history.
 * @author Konstantin Fadeyev
 *
 */
public class DictionariesManager implements WordsDataListener { 
	// DB related fields
	private Vector<Dictionary> dictionaries;
	
	// non-DB related fields
	private int wordsCount;
	private WordsDataListener wordsDataListener;
	
	// These indices are used to keep track of current word 
	// during cycling through words while testing
	private int currentDictionaryIndex;
	private int currentSheetIndex;
	private int currentWordIndex;
	
	
	/**
	 * Constructs DictionariesManager with default initial values
	 */		
	public DictionariesManager() {
		dictionaries = null;
		
		wordsCount = 0;
		currentDictionaryIndex = 0;
		currentSheetIndex = 0;
		currentWordIndex = 0;
		wordsDataListener = null;
	}

	
	/**
	 * Adds dictionary to DictionariesManager and insert record in database. 
	 * Inserts only dictionary record, doesn't insert sheet records.
	 * @param dictionary object of Dictionary
	 * @param storage object of GFIndicesStorage
	 */
	public synchronized void addDictionary(Dictionary dictionary, GFIndicesStorage storage) {	
	   	try {
			Connection connection = DatabaseManager.getConnection();      
			Statement statement = connection.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE,
			          ResultSet.CONCUR_UPDATABLE);
			statement.executeUpdate("INSERT INTO DICTIONARY " +
					"(NAME, ORDER_NUMBER) " +
					"VALUES " +
					"('" + Utils.prep(dictionary.getName(), 
							Settings.DICTIONARY_NAME_MAX_LENGTH) + "', " +
							+ size() + ")");
	    	statement.close();
			
			CallableStatement identityStatement = 
				connection.prepareCall("{?= CALL IDENTITY()}");
			ResultSet resultSet = identityStatement.executeQuery();	
			resultSet.next();
			dictionary.setId(resultSet.getInt(1));
			identityStatement.close();			
	    	connection.close();	    	
    	} catch (Exception e) {
	        e.printStackTrace();
	    }
		if (dictionaries == null) {
			loadDictionaries();
		}	
    	addDictionary(dictionary);
	}
	
	
	/**
	 * Empty implementation of WordsDataListener interface function
	 */
	public void elementInserted(int index) {}
	

	/**
	 * Exports all dictionaries and all related information into .xls file 
	 * @param file .xls file for export
	 * @return true, if export completed successfully
	 */
	public boolean exportToFile(File file) {
		try {
			WritableWorkbook workbook = Workbook.createWorkbook(file);
			WritableSheet excelSheet = workbook.createSheet("Dictionaries", 0);
			 
			WritableFont dictFont = 
				new WritableFont(WritableFont.ARIAL, 10, WritableFont.BOLD );
			WritableCellFormat dictFormat = new WritableCellFormat(dictFont);
			
			WritableFont sheetFont = 
				new WritableFont(WritableFont.ARIAL, 10, WritableFont.NO_BOLD, true );
			WritableCellFormat sheetFormat = new WritableCellFormat(sheetFont);
			
			WritableFont transFont = 
				new WritableFont(WritableFont.createFont("Lucida Sans Unicode"), 10);
			WritableCellFormat transFormat = new WritableCellFormat(transFont);
			
			DateFormat customDateFormat = new DateFormat("yyyy.MM.dd HH:mm:ss");
			WritableCellFormat dateFormat = new WritableCellFormat(customDateFormat);
			
			WritableCellFormat testTypeCellFormat = new WritableCellFormat();
			testTypeCellFormat.setAlignment(jxl.format.Alignment.RIGHT);

			int currentRow = 0;
			int maxHistorySize = 0;
			for(int i=0; i<size(); i++) {
				Dictionary dictionary = getDictionary(i);
				excelSheet.addCell(new Label(0, currentRow, 
						dictionary.getName(), dictFormat));
				
				currentRow++;
				for(int j=0; j<dictionary.size(); j++) {
					Sheet sheet = dictionary.getSheet(j);
					
					excelSheet.addCell(new Label(1, currentRow, 
							sheet.getName(), sheetFormat));
					currentRow++;
					for(int k=0; k<sheet.size(); k++) {
						WordRecord wordRecord = sheet.getWordRecord(k);
						if(k==0) {
							excelSheet.addCell(new Label(2, currentRow, 
									wordRecord.getWord()));
						} else {
							excelSheet.addCell(new Label(2, currentRow, 
									wordRecord.getWord()));							
						}
						excelSheet.addCell(new Label(3, currentRow, 
								wordRecord.getTranscription(), transFormat));
						excelSheet.addCell(new Label(4, currentRow, 
								wordRecord.getTranslation()));
						excelSheet.addCell(new Label(5, currentRow, 
								wordRecord.getPrompt()));
						
						excelSheet.addCell(new DateTime(6, currentRow, 
								new Date(wordRecord.getCreateDate()), dateFormat));
						
						int currentColumn = 7;
						if(wordRecord.getHistorySize() > maxHistorySize) {
							maxHistorySize = wordRecord.getHistorySize();
						}
						for(int l = 0; l < wordRecord.getHistorySize(); l++) {
							TestHistoryRecord record = 
								wordRecord.getHistoryRecord(l);
							try{
							excelSheet.addCell(new DateTime(currentColumn, currentRow, 
									new Date(record.getDate()), dateFormat));
							currentColumn++;
							
							String testType = "";
                        	switch(record.getTestType()) {
                        	case TestManager.TRANSLATION:
                        		testType = "TRANSLATION";
                        		break;
                        	case TestManager.REVERSE_TRANSLATION:
                        		testType = "REVERSE";
                        		break;
                        	case TestManager.GAPS_FILLING:
                        		testType = "GAPS_FILLING";
                        		break;
                        	case TestManager.TRANSCRIPTION:
                        		testType = "TRANSCRIPTION";
                        		break;
                        	}
							
							excelSheet.addCell(new Label(currentColumn, currentRow, 
									testType, testTypeCellFormat));
							currentColumn++;
							excelSheet.addCell(new jxl.write.Boolean(currentColumn, 
									currentRow, record.isSuccessful()));
							currentColumn++;
							} catch(Exception e) {
								e.printStackTrace();
							}
						}
						currentRow++;
					}
				}
			}
			excelSheet.setColumnView(0, 8);
			excelSheet.setColumnView(1, 8);
			excelSheet.setColumnView(2, 14);
			excelSheet.setColumnView(3, 13);
			excelSheet.setColumnView(4, 26);
			excelSheet.setColumnView(5, 70);
			excelSheet.setColumnView(6, 19);
			for(int i = 0; i < maxHistorySize; i++) {
				excelSheet.setColumnView(7 + i*3, 19);
				excelSheet.setColumnView(8 + i*3, 18);
			}
			workbook.write();
			workbook.close();
			return true;
		} catch(Exception e) {
			e.printStackTrace();
			return false;
		}
	}
	
	
	/**
	 * This function is one of four functions (startTestWordsIteration(), 
	 * hasNextTestWord(), getNextTestWord(), getCurrentIndex()) used to organize 
	 * cycling through word records while testing. Returns full index 
	 * (including dictionary, sheet and word indices) of current WordRecord.
	 * @return object of WordIndex
	 */
	public ThroughWordIndex getCurrentIndex() {
		ThroughWordIndex wordIndex = new ThroughWordIndex();
		wordIndex.setDictIndex(currentDictionaryIndex);
		wordIndex.setSheetIndex(currentSheetIndex);
		wordIndex.setWordIndex(currentWordIndex);
		return wordIndex;
	}
	

	/**
	 * Returns dictionary specified by index for this DictionariesManager
	 * @param index index of dictionary in DictionariesManager
	 * @return dictionary specified by index
	 */
	public Dictionary getDictionary(int index) {
		if (dictionaries == null) {
			loadDictionaries();
		}
		return (Dictionary)dictionaries.elementAt(index);
	}
	
	
	/**
	 * This function is one of four functions (startTestWordsIteration, 
	 * hasNextTestWord, getNextTestWord, getCurrentIndex) used to organize 
	 * cycling through word records while testing. Returns next WordRecord 
	 * that will be used for test.  
	 * @return next WordRecord for test
	 */
	public WordRecord getNextTestWord() {
		Dictionary currentDictionary = 
			getDictionary(currentDictionaryIndex);
		Sheet currentSheet = currentDictionary.getSheet(currentSheetIndex);	
		return currentSheet.getWordRecord(currentWordIndex);
	}
	
	
	/**
	 * Returns total number of word records that need to be tested
	 * @param testType one of TestManager test types
	 * @return total number of word records that need to be tested
	 */
	public int getTestWordsCount(int testType) {
		int result = 0;
		for(int i = 0; i < size(); i++) {
			Dictionary dictionary = (Dictionary)dictionaries.elementAt(i);
			result += dictionary.getTestWordsCount(testType);
		}
		return result;		
	}
	
	
	/**
	 * Returns number of word records that need to be tested but only 
	 * including those dictionaries that are chosen to test
	 * @param testType one of TestManager test types
	 * @return number of word records that need to be tested in marked 'to test' 
	 * dictionaries
	 */
	public int getToTestWordsCount(int testType) {
		int result = 0;
		for(int i = 0; i < size(); i++) {
			Dictionary dictionary = (Dictionary)dictionaries.elementAt(i);
			if(dictionary.isToTest()) {
				result += dictionary.getToTestWordsCount(testType);
			}
		}
		return result;		
	}
	
	
	/**
	 * Returns total number of word records in all dictionaries.
	 * @return total number of word records
	 */
	public int getWordsCount() {
		return wordsCount;
	}
	
	
	/**
	 * This function is one of four functions (startTestWordsIteration, 
	 * hasNextTestWord, getNextTestWord, getCurrentIndex) used to organize 
	 * cycling through word records while testing. Returns true if cycle 
	 * has next WordRecord for test.
	 * @param testType one of TestManager test types
	 * @return true if cycle has next WordRecord for test
	 */
	public boolean hasNextTestWord(int testType) {
		while(currentDictionaryIndex < size()) {
			Dictionary currentDictionary = getDictionary(currentDictionaryIndex);
			if(currentDictionary.isToTest()) {
				while(currentSheetIndex < currentDictionary.size()) {
					Sheet currentSheet = currentDictionary.getSheet(currentSheetIndex);
					if(currentSheet.isToTest()) {
						currentWordIndex++;
						while(currentWordIndex < currentSheet.size()) {
							WordRecord currentWord = 
								currentSheet.getWordRecord(currentWordIndex);
							if(currentWord.isNeededToTest(testType)) {
								return true;
							}
							currentWordIndex++;
						}
						currentWordIndex = -1;
					}
					currentSheetIndex++;
				}	
				currentSheetIndex = 0;
			}
			currentDictionaryIndex++;			
		}
		return false;
	}
	
	
	/**
	 * Imports dictionaries from .xls file. Imported dictionaries are just added
	 * to dictionariesManager. If row in .xls file doesn't meet required 
	 * format import function skip that row.
	 * @param file .xls file for import
	 * @param storage object of GFIndicesStorage
	 * @return true, if import completed successfully
	 */
	public synchronized boolean importFromFile(File file, GFIndicesStorage storage) {
		try {
			Workbook workbook = Workbook.getWorkbook(file);
			jxl.Sheet excelSheet = workbook.getSheet(0);
			
			int currentRow = 0;
			String[] currentLine = new String[5];

			Dictionary currentDictionary = null;
			Sheet currentSheet = null;
			int rowsNumber = excelSheet.getRows();
			int columnsNumber = excelSheet.getColumns();
			while(currentRow < rowsNumber){
				for(int i = 0; i < currentLine.length; i++) {
					currentLine[i] = 
						excelSheet.getCell(i, currentRow).getContents().trim();
				}
				
				if(!currentLine[0].equals("")) {
					if(currentDictionary != null) {
						if(currentSheet != null) {
							currentSheet.setWordsCount(currentSheet.size());
			        		for(int j=0; j<TestManager.TESTS_NUMBER; j++) {
			        			currentSheet.setUncheckedWordsCount(
			        					currentSheet.size(), j);
			        			currentDictionary.uncheckedWordsCount[j] 
			        			                        += currentSheet.size();
			        		}
							currentDictionary.addSheet(currentSheet);
							currentDictionary.setWordsCount(
									currentDictionary.getWordsCount() + 
									currentSheet.size());
						}
						addDictionary(currentDictionary, storage);
						currentDictionary.insertSheetsToDB(storage);						
						if(wordsDataListener != null) {
				    		wordsDataListener.elementInserted(size()-1);
						}
					}
					currentDictionary = new Dictionary();
					currentDictionary.setName(currentLine[0]);
					currentSheet = null;
				} else if(!currentLine[1].equals("") 
						&& (currentDictionary != null)){
					if(currentSheet != null) {
						currentSheet.setWordsCount(currentSheet.size());
		        		for(int j=0; j<TestManager.TESTS_NUMBER; j++) {
		        			currentSheet.setUncheckedWordsCount(
		        					currentSheet.size(), j);
		        			currentDictionary.uncheckedWordsCount[j] 
		        			                          += currentSheet.size();
		        		}
						currentDictionary.addSheet(currentSheet);
						currentDictionary.setWordsCount(
								currentDictionary.getWordsCount() 
								+ currentSheet.size());
					}
					currentSheet = new Sheet();
					currentSheet.setName(currentLine[1]);
				} else if((!currentLine[2].equals("") || 
						!currentLine[3].equals("") || !currentLine[4].equals("")) 
						&& (currentSheet != null)){					
					WordRecord wordRecord = new WordRecord();
					wordRecord.setWord(currentLine[2]);
					wordRecord.setTranscription(currentLine[3]);
					wordRecord.setTranslation(currentLine[4]);
					String prompt = 
						excelSheet.getCell(5, currentRow).getContents().trim();
					wordRecord.setPrompt(prompt);
					Cell createDateCell = excelSheet.getCell(6, currentRow); 
					long createDate;
					if(createDateCell.getType() == CellType.DATE)
					{
						DateCell dc = (DateCell)createDateCell;
						TimeZone timeZone = TimeZone.getDefault();						
						createDate = dc.getDate().getTime();
						int offset = timeZone.getOffset(createDate);
						createDate -=offset;
					} else {
						createDate = System.currentTimeMillis();
					}
					wordRecord.setCreateDate(createDate);
					
					int currentColumn = 7;
					
					while(currentColumn < columnsNumber) {
						Cell dateCell = 
							excelSheet.getCell(currentColumn, currentRow);
						long date;
						if(dateCell.getType() == CellType.DATE) {
							DateCell dc = (DateCell)dateCell;
							TimeZone timeZone = TimeZone.getDefault();
							date = dc.getDate().getTime();
							int offset = timeZone.getOffset(createDate);
							createDate -=offset;
						} else {
							break;
						}					
						currentColumn++;
						
						String testTypeCell = excelSheet.getCell(currentColumn, 
								currentRow).getContents().trim();
						int testType;
                    	if(testTypeCell.equals("TRANSLATION")) {
                    		testType = TestManager.TRANSLATION;
                    	} else if(testTypeCell.equals("REVERSE")) {
                    		testType = TestManager.REVERSE_TRANSLATION;
                    	} else if(testTypeCell.equals("GAPS_FILLING")) {
                    		testType = TestManager.GAPS_FILLING;
                    	} else if(testTypeCell.equals("TRANSCRIPTION")) {
                    		testType = TestManager.TRANSCRIPTION;
                    	} else {
                    		break;
                    	}
                    	currentColumn++;
                    	
                    	Cell testResultCell = 
                    		excelSheet.getCell(currentColumn, currentRow);
                    	boolean testResult;
                    	if(testResultCell.getType() == CellType.BOOLEAN) {
                    		BooleanCell bc = (BooleanCell)testResultCell;
                    		testResult = bc.getValue();
                    	} else {
                    		break;
                    	}
                    	currentColumn++;
                    	
                    	TestHistoryRecord record = 
                    		new TestHistoryRecord(date, testType, testResult);
                    	wordRecord.addHistoryRecord(record);
					}
					
					currentSheet.addWordRecord(wordRecord);
				}
				currentRow++;
			}
			if(currentDictionary != null) {
				if(currentSheet != null) {
					currentSheet.setWordsCount(currentSheet.size());
	        		for(int j=0; j<TestManager.TESTS_NUMBER; j++) {
	        			currentSheet.setUncheckedWordsCount(currentSheet.size(), j);
	        			currentDictionary.uncheckedWordsCount[j] += currentSheet.size();
	        		}
					currentDictionary.addSheet(currentSheet);
					currentDictionary.setWordsCount(
							currentDictionary.getWordsCount() + 
							currentSheet.size());
				}
				addDictionary(currentDictionary, storage);
				currentDictionary.insertSheetsToDB(storage);				
				if(wordsDataListener != null) {
		    		wordsDataListener.elementInserted(size()-1);
				}
			}
			workbook.close();
			return true;
		} catch(Exception e) {
			e.printStackTrace();
			return false;
		}
	}
	
	
	/**
	 * Removes dictionary specified by 'index' and all related information 
     * from DictionariesManager and from database.
	 * @param index index of dictionary to remove
	 * @param storage object of GFIndicesStorage
	 */
	public synchronized void removeDictionary(int index, GFIndicesStorage storage) {
		Dictionary dictionary = getDictionary(index);
		dictionary.removeAllSheetsFromDB(storage);
		try {		
			Connection connection = DatabaseManager.getConnection();      
			Statement statement = connection.createStatement();
			statement.executeQuery(
        			"DELETE FROM DICTIONARY " +
        			"WHERE ID = " + dictionary.getId()
        			);	 
        	statement.close();
        	connection.close();
        	setWordsCount(wordsCount - dictionary.getWordsCount());
    		dictionaries.removeElementAt(index);	
		} catch (Exception e) {
	        e.printStackTrace();
	    }	
	}
	
	
	/**
	 * Saves to database information about what dictionaries and sheets 
	 * are chosen 'to test'.
	 */
	public void saveToTestValues() {
    	try {
			Connection connection = DatabaseManager.getConnection();     
			PreparedStatement dictStatement = connection.prepareStatement(
	    			"UPDATE DICTIONARY " +
	    			"SET TO_TEST = ? " +
	    			"WHERE ID = ?");

			PreparedStatement sheetStatement = connection.prepareStatement(
	    			"UPDATE SHEET " +
	    			"SET TO_TEST = ? " +
	    			"WHERE ID = ?");

	    	for(int k=0; k < size(); k++) {
	    		Dictionary dictionary = getDictionary(k);
	    		if(dictionary.isToTest()) {
	    			dictStatement.setBoolean(1, true);
	    			dictStatement.setInt(2, dictionary.getId());
	    			dictStatement.addBatch();	
	    		} else {
	    			dictStatement.setBoolean(1, false);
	    			dictStatement.setInt(2, dictionary.getId());
	    			dictStatement.addBatch();		    			
	    		}
	    		for(int i=0; i < dictionary.size(); i++) {
	        		Sheet sheet = dictionary.getSheet(i);
		    		if(sheet.isToTest()) {
		    			sheetStatement.setBoolean(1, true);
		    			sheetStatement.setInt(2, sheet.getId());
		    			sheetStatement.addBatch();	
		    		} else {
		    			sheetStatement.setBoolean(1, false);
		    			sheetStatement.setInt(2, sheet.getId());
		    			sheetStatement.addBatch();		    			
		    		}
	    		}
	    	}
	    	dictStatement.executeBatch();
	    	sheetStatement.executeBatch();
			dictStatement.close();
			sheetStatement.close();
			connection.close();
    	} catch(Exception e) {
    		e.printStackTrace();
    	}
	}
	

	/**
     * Sets new WordsDataListener
     * @param listener new WordsDataListener
     */
	public void setWordsDataListener(WordsDataListener listener) {
		wordsDataListener = listener;
	}
	
	
	/**
	 * Returns number of dictionaries in DictionariesManager
	 */
	public int size() {
		if (dictionaries == null) {
			loadDictionaries();
		}
		return dictionaries.size();
	}
	
	
	/**
	 * This function is one of four functions (startTestWordsIteration, 
	 * hasNextTestWord, getNextTestWord, getCurrentIndex) used to organize 
	 * cycling through word records while testing. 
	 * Initializes inner variables for cycling.
	 */
	public void startTestWordsIteration() {
		currentDictionaryIndex = 0;
		currentSheetIndex = 0;
		currentWordIndex = -1;
	}
	
	
	/**
     * Implements WordsDataListener interface testWordsCountChanged function
	 */
	public void testWordsCountChanged(int change, int testType, 
    		boolean unchecked, int dictionaryIndex, int sheetIndex) {   	
    	if(wordsDataListener != null) {
    		wordsDataListener.testWordsCountChanged(change, testType, 
					unchecked, dictionaryIndex, sheetIndex);
		}
    }
	
	
	/**
	 * Update database information for dictionary specified by 'index' 
	 * @param index index of dictionary to update database for
	 */
	public void updateDictionary(int index) {
		Dictionary dictionary = getDictionary(index);
    	try {
			Connection connection = DatabaseManager.getConnection();      
			Statement statement = 
				connection.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE,
			          ResultSet.CONCUR_UPDATABLE);
			statement.executeUpdate("UPDATE DICTIONARY " +
					"SET NAME = '" + Utils.prep(dictionary.getName(), 
							Settings.DICTIONARY_NAME_MAX_LENGTH) + "'" +
					"WHERE ID = " + dictionary.getId());
	    	statement.close();
	    	connection.close();	    	

    	} catch (Exception e) {
	        e.printStackTrace();
	    }	
	}
	
	
	/**
     * Implements WordsDataListener interface testWordsCountChanged function
	 */
	public void wordsCountChanged(int change) {
		setWordsCount(wordsCount + change);
	}
	
	
	/**
	 * Sets total number of words. Used by 'wordsCountChanged(int change)' method 
	 * implementing WordsDataListener interface 
	 * @param value new total number of words
	 */
	void setWordsCount(int value) {
		int change = value - wordsCount;
		wordsCount = value;
		if(wordsDataListener != null) {
			wordsDataListener.wordsCountChanged(change);
		}
	}
	
	/**
	 * Adds dictionary to DictionariesManager without saving to database. 
	 * Used by internal functions.
	 * @param dictionary added dictionary
	 */
	private void addDictionary(Dictionary dictionary) {
		if(dictionaries == null) {
			dictionaries = new Vector<Dictionary>();
		}
		dictionaries.addElement(dictionary);
		setWordsCount(wordsCount + dictionary.getWordsCount());
		dictionary.setWordsDataListener(this);
	}
	
	/**
	 * Loads dictionaries from database (but doesn't load any sheets information). 
	 */
	private void loadDictionaries() {
		dictionaries = new Vector<Dictionary>();			
		try {
			Connection connection = DatabaseManager.getConnection();      
			Statement statement = connection.createStatement();
			ResultSet resultSet = statement.executeQuery(
		        			"SELECT ID, NAME, TO_TEST " +		        				
		        			"FROM DICTIONARY " +
		        			"ORDER BY ORDER_NUMBER"
		        			);	
        	while(resultSet.next()) {
        		Dictionary newDictionary = new Dictionary();      		
        		int id = resultSet.getInt(1);
        		ResultSet wordsCountResultSet = statement.executeQuery(
        				"SELECT COUNT(*) " +
        	        	"FROM WORD_RECORD INNER JOIN " +
        	        		"(SELECT ID FROM SHEET " +
        	        		"WHERE DICT_ID = " + id + ") AS SHEET_IDS " +
        	        	"ON (WORD_RECORD.SHEET_ID = SHEET_IDS.ID) "
	        			);	
        		wordsCountResultSet.next();
        		newDictionary.setId(id);
        		newDictionary.setName(resultSet.getString(2));
        		newDictionary.setToTest(resultSet.getBoolean(3));
        		int wordsCount = wordsCountResultSet.getInt(1);
        		newDictionary.setWordsCount(wordsCount);
        		for(int i=0; i<TestManager.TESTS_NUMBER; i++) {
        			newDictionary.setUncheckedWordsCount(wordsCount, i);
        		}
        		addDictionary(newDictionary);
        	}      	
			statement.close();
        	connection.close();        	
		} catch (Exception e) {
	        e.printStackTrace();
	    }		
	}
}

